from database.connection import execute_query

# Complex Query 1: List all appointments with patient names, doctor names, and department
def list_appointments_with_details():
    query = """
        SELECT a.appointment_id, p.name AS patient_name, s.name AS doctor_name, d.name AS department, a.date, a.time
        FROM Appointments a
        INNER JOIN Patients p ON a.patient_id = p.patient_id
        INNER JOIN Medical_Staff s ON a.staff_id = s.staff_id
        LEFT JOIN Departments d ON s.department_id = d.department_id
    """
    return execute_query(query, fetch=True)

# Complex Query 2: Calculate total revenue per department
def calculate_revenue_per_department():
    query = """
        SELECT d.name AS department, SUM(i.amount) AS total_revenue
        FROM Invoices i
        INNER JOIN Appointments a ON i.appointment_id = a.appointment_id
        INNER JOIN Medical_Staff s ON a.staff_id = s.staff_id
        INNER JOIN Departments d ON s.department_id = d.department_id
        WHERE i.status = 'paid'
        GROUP BY d.name
        HAVING SUM(i.amount) > 0
    """
    return execute_query(query, fetch=True)

# Complex Query 3: Find doctors with more than 5 appointments this month
def find_busy_doctors(x=5):
    query = """
        WITH current_month_appointments AS (
            SELECT staff_id, COUNT(*) AS count
            FROM Appointments
            WHERE date >= DATE_TRUNC('month', CURRENT_DATE) AND date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
            GROUP BY staff_id
        )
        SELECT s.name AS doctor_name, c.count AS appointment_count
        FROM current_month_appointments c
        INNER JOIN Medical_Staff s ON c.staff_id = s.staff_id
        WHERE c.count > %s
    """
    return execute_query(query, (x,), fetch=True)